﻿using System;
using System.Data;
using System.Collections;
using CE.Database;

/// <summary>
/// Summary description for NonGGPackageDB
/// </summary>
namespace CE.CarInsurance
{
    public class NonGGPackageDB
    {
        public Int64 Insert(NonGGPackage nonggpackage)
        {
            return Insert(nonggpackage, false);
        }

        public Int64 Insert(NonGGPackage nonggpackage, Boolean IsReference)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            Int64 _result = -1;
            string _spName = "sprocCXCRMNONGGPackageCarDetailsInsertUpdateSingleItem";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@CECarId", nonggpackage.CECarId);
            _sPara.Add("@ProviderCompanyId", nonggpackage.ProviderCompanyId);
            _sPara.Add("@Title", nonggpackage.Title);
            _sPara.Add("@CustomerType", nonggpackage.CustomerType);
            _sPara.Add("@CustomerName", nonggpackage.CustomerName);
            _sPara.Add("@Name", nonggpackage.Name);
            _sPara.Add("@DOB", nonggpackage.DOB);
            _sPara.Add("@Gender", nonggpackage.Gender);
            _sPara.Add("@Occupation", nonggpackage.Occupation);
            _sPara.Add("@ContactNo", nonggpackage.ContactNo);
            _sPara.Add("@OfficePhoneNo", nonggpackage.OfficePhoneNo);
            _sPara.Add("@IdentificationNo", nonggpackage.IdentificationNo);
            _sPara.Add("@PresentAddress", nonggpackage.PresentAddress);
            _sPara.Add("@SubDistrict", nonggpackage.SubDistrict);
            _sPara.Add("@District", nonggpackage.District);
            _sPara.Add("@Province", nonggpackage.Province);
            _sPara.Add("@PostalCode", nonggpackage.PostalCode);
            _sPara.Add("@Package", nonggpackage.Package);
            _sPara.Add("@NoOfDriver", nonggpackage.NoOfDriver);
            _sPara.Add("@InsuranceType", nonggpackage.InsuranceType);
            _sPara.Add("@GarageType", nonggpackage.GarageType);
            _sPara.Add("@CarYear", nonggpackage.CarYear);
            _sPara.Add("@CarModel", nonggpackage.CarModel);
            //_sPara.Add("@Size", nonggpackage.Size);
            //_sPara.Add("@VehicleWeight", nonggpackage.VehicleWeight);
            //_sPara.Add("@NumberofSeat", nonggpackage.NumberofSeat);
            _sPara.Add("@Beneficiary", nonggpackage.Beneficiary);
            _sPara.Add("@VehicleType", nonggpackage.VehicleType);
            _sPara.Add("@SumInsured", nonggpackage.SumInsured);
            _sPara.Add("@NCB", nonggpackage.NCB);
            _sPara.Add("@CurrentInsurer", nonggpackage.CurrentInsurer);
            _sPara.Add("@CarPlate", nonggpackage.CarPlate);
            _sPara.Add("@CarPlateProvince", nonggpackage.CarPlateProvince);
            _sPara.Add("@EngineNumber", nonggpackage.EngineNumber);
            _sPara.Add("@ChassisNumber", nonggpackage.ChassisNumber);
            //_sPara.Add("@IsModification", nonggpackage.IsModification);
            //_sPara.Add("@IsClaim", nonggpackage.IsClaim);
            _sPara.Add("@CoverageStart", nonggpackage.CoverageStart);
            _sPara.Add("@Premium", nonggpackage.Premium);
            _sPara.Add("@IsCTPL", nonggpackage.IsCTPL);
            _sPara.Add("@CTPL", nonggpackage.CTPL);
            _sPara.Add("@CTPLStart", nonggpackage.CTPLStart);
            _sPara.Add("@TotalCollectedPremium", nonggpackage.TotalCollectedPremium);
            _sPara.Add("@TotalPremium", nonggpackage.TotalPremium);
            _sPara.Add("@TermPremium", nonggpackage.TermPremium);
            _sPara.Add("@PaymentStatus", nonggpackage.PaymentStatus);
            _sPara.Add("@QuoteRefNo", nonggpackage.QuoteRefNo);
            _sPara.Add("@Status", nonggpackage.Status);
            _sPara.Add("@RefCEUserId", nonggpackage.RefCEUserId);
            _sPara.Add("@AgentId", nonggpackage.AgentId);
            _sPara.Add("@AdminId", nonggpackage.AdminId);
            _sPara.Add("@Info1", nonggpackage.Info1); // Application Type
            _sPara.Add("@Info2", nonggpackage.Info2); // Last Year Policy No.
            _sPara.Add("@Info3", nonggpackage.Info3);
            _sPara.Add("@IsCreditCardOwner", nonggpackage.IsCreditCardOwner); // tblCEUsrProfile table Ninfo5 field
            _sPara.Add("@Info4", nonggpackage.Info4); //Accessories or Modifications Cost
            _sPara.Add("@Info8", nonggpackage.Info8); //Agent Remarks
            _sPara.Add("@PromotionCode", nonggpackage.PromotionCode);
            _sPara.Add("@IsMailingAddressDiff", nonggpackage.IsMailingAddress);
            _sPara.Add("@MailingAddress", nonggpackage.MailingAddress);
            _sPara.Add("@MailingSubDistrict", nonggpackage.MailingSubDistrict);
            _sPara.Add("@MailingDistrict", nonggpackage.MailingDistrict);
            _sPara.Add("@MailingProvince", nonggpackage.MailingProvince);
            _sPara.Add("@MailingPostalCode", nonggpackage.MailingPostalCode);
            _sPara.Add("@MailingName", nonggpackage.MailingName);
            _sPara.Add("@Ninfo5", nonggpackage.Mile);
            if(IsReference) _sPara.Add("@IsReference", IsReference);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public Int64 InsertByAdmin(NonGGPackage nonggpackage)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            Int64 _result = -1;
            string _spName = "sprocCXCRMNONGGPackageCarDetailsInsertUpdatebyAdmin";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@CECarId", nonggpackage.CECarId);
            _sPara.Add("@ProviderCompanyId", nonggpackage.ProviderCompanyId);
            _sPara.Add("@Title", nonggpackage.Title);
            _sPara.Add("@CustomerType", nonggpackage.CustomerType);
            _sPara.Add("@CustomerName", nonggpackage.CustomerName);
            _sPara.Add("@Name", nonggpackage.Name);
            _sPara.Add("@DOB", nonggpackage.DOB);
            _sPara.Add("@Gender", nonggpackage.Gender);
            _sPara.Add("@Occupation", nonggpackage.Occupation);
            _sPara.Add("@ContactNo", nonggpackage.ContactNo);
            _sPara.Add("@PresentAddress", nonggpackage.PresentAddress);
            _sPara.Add("@SubDistrict", nonggpackage.SubDistrict);
            _sPara.Add("@District", nonggpackage.District);
            _sPara.Add("@Province", nonggpackage.Province);
            _sPara.Add("@PostalCode", nonggpackage.PostalCode);
            _sPara.Add("@Package", nonggpackage.Package);
            _sPara.Add("@NoOfDriver", nonggpackage.NoOfDriver);
            _sPara.Add("@InsuranceType", nonggpackage.InsuranceType);
            _sPara.Add("@GarageType", nonggpackage.GarageType);
            _sPara.Add("@CarYear", nonggpackage.CarYear);
            _sPara.Add("@CarModel", nonggpackage.CarModel);
            //_sPara.Add("@Size", nonggpackage.Size);
            //_sPara.Add("@VehicleWeight", nonggpackage.VehicleWeight);
            //_sPara.Add("@NumberofSeat", nonggpackage.NumberofSeat);
            _sPara.Add("@Beneficiary", nonggpackage.Beneficiary);
            _sPara.Add("@VehicleType", nonggpackage.VehicleType);
            _sPara.Add("@SumInsured", nonggpackage.SumInsured);
            _sPara.Add("@NCB", nonggpackage.NCB);
            _sPara.Add("@CurrentInsurer", nonggpackage.CurrentInsurer);
            _sPara.Add("@CarPlate", nonggpackage.CarPlate);
            _sPara.Add("@CarPlateProvince", nonggpackage.CarPlateProvince);
            //_sPara.Add("@EngineNumber", nonggpackage.EngineNumber);
            _sPara.Add("@ChassisNumber", nonggpackage.ChassisNumber);
            //_sPara.Add("@IsModification", nonggpackage.IsModification);
            //_sPara.Add("@IsClaim", nonggpackage.IsClaim);
            _sPara.Add("@CoverageStart", nonggpackage.CoverageStart);
            _sPara.Add("@Premium", nonggpackage.Premium);
            _sPara.Add("@IsCTPL", nonggpackage.IsCTPL);
            _sPara.Add("@CTPL", nonggpackage.CTPL);
            _sPara.Add("@CTPLStart", nonggpackage.CTPLStart);
            _sPara.Add("@TotalCollectedPremium", nonggpackage.TotalCollectedPremium);
            _sPara.Add("@TotalPremium", nonggpackage.TotalPremium);
            _sPara.Add("@TermPremium", nonggpackage.TermPremium);
            _sPara.Add("@PaymentStatus", nonggpackage.PaymentStatus);
            _sPara.Add("@QuoteRefNo", nonggpackage.QuoteRefNo);
            _sPara.Add("@Status", nonggpackage.Status);
            _sPara.Add("@RefCEUserId", nonggpackage.RefCEUserId);
            _sPara.Add("@AgentId", nonggpackage.AgentId);
            _sPara.Add("@AdminId", nonggpackage.AdminId);
            _sPara.Add("@IsMailingAddressDiff", nonggpackage.IsMailingAddress);
            _sPara.Add("@MailingAddress", nonggpackage.MailingAddress);
            _sPara.Add("@MailingSubDistrict", nonggpackage.MailingSubDistrict);
            _sPara.Add("@MailingDistrict", nonggpackage.MailingDistrict);
            _sPara.Add("@MailingProvince", nonggpackage.MailingProvince);
            _sPara.Add("@MailingPostalCode", nonggpackage.MailingPostalCode);
            _sPara.Add("@MailingName", nonggpackage.MailingName);
            _sPara.Add("@Ninfo5", nonggpackage.Mile);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public DataSet GetCarDetails(Int64 CarId)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocCXCRMNONGGPackageCarDetailsSelectedItemByCarId]";

            SortedList _spara = new SortedList();
            _spara.Add("@CarId", CarId);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetCarDetails(Int64 CarId, Int64 CustomerId)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocCXCRMNONGGPackageCarDetailsSelectedItemByCarId]";

            SortedList _spara = new SortedList();
            _spara.Add("@CarId", CarId);
            _spara.Add("@CustomerId", CustomerId);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetCarDetails(string QuoteRefNo)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocCXCRMNONGGPackageCarDetailsSelectedItemByQuoteRefNo]";

            SortedList _spara = new SortedList();
            _spara.Add("@QuoteRefNo", QuoteRefNo);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public DataSet GetLastYearCarDetails(Int64 CarId, string LYrQuoteRefNo)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocCXCRMNONGGPackageExsitingItemSelectByQuoteRefNo]";

            SortedList _spara = new SortedList();
            _spara.Add("@CarId", CarId);
            _spara.Add("@LYrQuoteRefNo", LYrQuoteRefNo);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public Int64 UpdatePaymentStatus(Int64 CECarId, Int64 ProviderId, string ReceiptNo, Int64 AgentId, string PaymentType, string PayBy)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMNonGGPaymentStatusUpdatedSelectItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@CECarId", CECarId);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@ReceiptNo", ReceiptNo);
            _sPara.Add("@AgentId", AgentId);
            _sPara.Add("@PaymentType", PaymentType);
            _sPara.Add("@PayBy", PayBy);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public Int64 UpdatePaymentStatus(Int64 CECarId, Int64 ProviderId, string ReceiptNo, Int64 AgentId, string PaymentType, string PayBy, string Excel)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMNonGGPaymentStatusUpdatedSelectItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@CECarId", CECarId);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@ReceiptNo", ReceiptNo);
            _sPara.Add("@AgentId", AgentId);
            _sPara.Add("@PaymentType", PaymentType);
            _sPara.Add("@PayBy", PayBy);
            _sPara.Add("@Excel", Excel);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public DataSet GetNonGGApplication(Int64 CECarId, Int64 ProviderId, Int16 IsInternal)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocCXCRMNonGGApplicationSelectItem]";

            SortedList _spara = new SortedList();
            _spara.Add("@CECarId", CECarId);
            _spara.Add("@ProviderCompanyId", ProviderId);
            _spara.Add("@IsInternal", IsInternal);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public Int64 UpdateApplicationCancelStatus(Int64 CECarId, Int64 ProviderId, Int64 AgentId, Int16 Step, string Remarks)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMNonGGApplicationCancelUpdateSelectItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@CECarId", CECarId);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@AgentId", AgentId);
            _sPara.Add("@Step", Step);
            _sPara.Add("@Remarks", Remarks);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public Int64 UpdateApplicationRetrieveStatus(Int64 CECarId, Int64 ProviderId, Int64 AgentId, Int16 Step)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMNonGGApplicationCancelUpdateSelectItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@CECarId", CECarId);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@AgentId", AgentId);
            _sPara.Add("@Step", Step);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public Int64 UpdateApplicationRetrieveStatus(Int64 CECarId, Int64 ProviderId, Int64 AgentId, Int16 Step, String QuoteRefNo)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMNonGGApplicationCancelUpdateSelectItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@CECarId", CECarId);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@AgentId", AgentId);
            _sPara.Add("@Step", Step);
            _sPara.Add("@QuoteRefNo", QuoteRefNo);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public DataSet GetCompletedUserItem(Int64 CECarId, Int64 RefCEUserId, Int64 ProviderCompanyId)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            string _spName = "[sprocCXCRMNonGGCarMoreDetailSelectSingleItem]";

            SortedList _spara = new SortedList();
            _spara.Add("@CECarId", CECarId);
            _spara.Add("@RefCEUserId", RefCEUserId);
            _spara.Add("@ProviderCompanyId", ProviderCompanyId);

            DataSet _ds = _db.GetDataSet(_spName, _spara);

            return _ds;
        }

        public Int64 UpdateExcelName(Int64 CECarId, string Excel)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCXCRMNonGGExcelNameUpdatedSelectItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@CECarId", CECarId);
            _sPara.Add("@Excel", Excel);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public Int64 DeclineOnlinePayment(Int64 CECarId, Int64 ProviderId, Int64 AgentId)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            Int64 _result = -1;
            string _spName = "sprocCXCRMOnlinePaymentDelineStatusUpdateSingleItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@CECarId", CECarId);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@AgentId", AgentId);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public Int64 DeclineNonGGOnlinePayment(Int64 CECarId, Int64 ProviderId, Int64 AgentId)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            Int64 _result = -1;
            string _spName = "sprocCXCRMNonGGOnlinePaymentDelineStatusUpdateSingleItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@CECarId", CECarId);
            _sPara.Add("@ProviderCompanyId", ProviderId);
            _sPara.Add("@AgentId", AgentId);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public Int64 DeclineNonGGPayment(Int64 CECarId, Int64 AdminId, Int64 PaymentId)
        {
            MyDatabase _db = new MyDatabase("GG_CRMDBConnectionString");
            Int64 _result = -1;
            string _spName = "sprocCXCRMNonGGPaymentDelineStatusUpdateSingleItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@CECarId", CECarId);
            _sPara.Add("@AdminId", AdminId);
            _sPara.Add("@PaymentId", PaymentId);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

    }
}